package com.jrauto.CarAppBackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.jrauto.CarAppBackend.entity.Car;
import com.jrauto.CarAppBackend.entity.vo.CarListVO;
import com.jrauto.CarAppBackend.entity.vo.CarVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
* @author huimin
* @description 针对表【cars(二手车主表)】的数据库操作Mapper
* @createDate 2025-06-27 15:58:44
* @Entity generator.domain.Cars
*/
@Mapper
public interface CarMapper extends BaseMapper<Car> {
    /**
     * 根据车辆ID查询车辆详情，并联合查询其所有媒体信息。
     * 这是一个自定义的联表查询，将媒体信息也查出来。
     * @param carId 车辆ID
     * @return 包含媒体列表的 Car 对象
     */
    @Select("SELECT c.*, cm.id as media_id, cm.type as media_type, cm.url as media_url, cm.created_at as media_created_at, cm.order_index as media_is_main, cm.order_index as media_order_index " +
            "FROM cars c LEFT JOIN car_media cm ON c.car_id = cm.car_id " +
            "WHERE c.car_id = #{carId} ORDER BY cm.order_index ASC")
    List<Car> selectCarWithMediaById(@Param("carId") Integer carId); // 返回List，因为一个Car可能有多条media记录
    // 注意：Mybatis-Plus 的 BaseMapper.selectById 不支持直接填充 @TableField(exist = false) 的 List 字段，
    // 所以这里需要自定义一个联表查询。在Service层进行数据组装。
    List<Car> selectByPublisherId(Integer userId);


    /**
     * 查询已上架的汽车列表，并包含主图URL
     *
     * @param page 分页对象
     * @return 汽车列表（包含主图URL）
     */
    @Select("SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
            "FROM cars c WHERE c.status = 1 ORDER BY c.updated_at DESC")
    IPage<CarListVO> selectPublishedCarsWithMainImage(IPage<CarListVO> page);


    /**
     * 查询已上架的汽车列表，并包含主图URL
     * @return 汽车列表（包含主图URL）
     */
    @Select("SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
            "FROM cars c WHERE c.status = 1")
    List<CarVO> queryByPublishId();

    /**
     * 根据标题模糊查询已上架的汽车列表，并包含主图URL
     * @param title 标题关键字
     * @return 汽车列表（包含主图URL）
     */
//    @Select("SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
//            "FROM cars c WHERE c.status = 1 AND c.title LIKE CONCAT('%', #{title}, '%')")
//    IPage<CarVO> selectPublishedCarsByTitleFuzzy( @Param("province") String province,
//                                                  @Param("city") String city,
//                                                  @Param("district") String district,
//                                                  @Param("title") String title,
//                                                  IPage<CarVO> page);

    /**
     * 根据汽车编号查询已上架的汽车列表，并包含主图URL
     * @param carNumber 汽车编号
     * @return 汽车列表（包含主图URL）
     */
    @Select("SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
            "FROM cars c WHERE c.status = 1 AND c.car_number = #{carNumber}")
    List<CarVO> selectPublishedCarsByCarNumber(@Param("carNumber") String carNumber);

    /**
     * 根据汽车品牌查询已上架的汽车列表，并包含主图URL
     * @param brand 汽车品牌
     * @return 汽车列表（包含主图URL）
     */
    @Select("SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
            "FROM cars c WHERE c.status = 1 AND c.brand = #{brand}")
    IPage<CarListVO> selectPublishedCarsByBrand(@Param("brand") String brand,IPage<CarListVO> page);

    /**
     * 根据地区（省市区）查询已上架的汽车列表，并包含主图URL
     * @param province 省份
     * @param city 城市
     * @param district 区县
     * @return 汽车列表（包含主图URL）
     */
    @Select("<script>" +
            "SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl " +
            "FROM cars c WHERE c.status = 1 " +
            "<if test='province != null and province != \"\"'> AND c.province = #{province}</if>" +
            "<if test='city != null and city != \"\"'> AND c.city = #{city}</if>" +
            "<if test='district != null and district != \"\"'> AND c.district = #{district}</if>" +
            "</script>")
    List<CarVO> selectPublishedCarsByRegion(
            @Param("province") String province,
            @Param("city") String city,
            @Param("district") String district);


    // CarMapper.java 接口 - 使用 @Select
// 注意：这个SQL片段假设您使用的数据库支持IF()函数，例如MySQL。
    @Select({
            "SELECT c.*, (SELECT url FROM car_media cm WHERE cm.car_id = c.car_id AND cm.order_index = 1 LIMIT 1) AS mainImageUrl ",
            "FROM cars c ",
            "WHERE c.status = 1 ",
            "AND c.title LIKE CONCAT('%', #{title}, '%') ",
            // 增加对省份的过滤: 如果province为空，则条件始终为真 (1=1)，否则按province过滤
            "AND (IF(#{province} IS NULL OR #{province} = '', 1=1, c.province = #{province})) ",
            // 增加对城市的过滤
            "AND (IF(#{city} IS NULL OR #{city} = '', 1=1, c.city = #{city})) ",
            // 增加对区县的过滤
            "AND (IF(#{district} IS NULL OR #{district} = '', 1=1, c.district = #{district})) "
    })
    IPage<CarListVO> selectPublishedCarsByRegionAndTitleFuzzy(
            @Param("province") String province,
            @Param("city") String city,
            @Param("district") String district,
            @Param("title") String title,
            IPage<CarListVO> page
    );
}




